const db = require("../utils/db");
const util = require("../utils/util")

async function search_fullposts(user_id, text, start, count, options) {
    var insert_1 = "", insert_2 = "";
    if (options.mine_only) insert_1 = `post.user_id = '${user_id}' and`;
    if (options.star_only) insert_2 = `WHERE stars.starred = true`
    var insert_order = options.method == 'time' ? `ORDER BY p.post_time DESC` : `ORDER BY fever DESC`

    // 搜索post，同时把user，upvote、star、comment相关信息补全后返回
    var sql = 
`
SELECT
    p.*,
	upvotes.upvote_num,
    upvotes.upvoted,
    stars.star_num,
    stars.starred,
    comments.comment_num,
						
	ROUND((p.read_num +
	IFNULL(upvotes.upvote_num, 0) * 3 +
	IFNULL(comments.comment_num, 0) * 7 +
	IFNULL(stars.star_num, 0) * 10) * 10 / (10 + TIMESTAMPDIFF(DAY,p.post_time,NOW()))) as fever
FROM
	(
        -- 先筛选，再连接
		SELECT
			post.*,
			user.name AS user_name,
			user.avatar AS user_avatar
		FROM post, user
		WHERE
            ${insert_1}
            (content like '%${text}%' or title like '%${text}%' or tags like '%${text}%') and
            post.user_id = user.user_id
	) p LEFT OUTER JOIN
    (
        SELECT
            post_id,
            count(*) AS upvote_num,
            if(sum(user_id = '${user_id}'), true, false) AS upvoted
        FROM upvote_post
        GROUP BY post_id
    ) upvotes
    ON p.post_id = upvotes.post_id
    LEFT OUTER JOIN
    (
		SELECT
            post_id,
            count(*) AS star_num,
            if(sum(user_id = '${user_id}'), true, false) AS starred
        FROM star
        GROUP BY post_id
    ) stars
    ON p.post_id = stars.post_id
    LEFT OUTER JOIN
    (
        SELECT
            post_id,
            count(*) AS comment_num
        FROM comment
        GROUP BY post_id
    ) comments
    ON p.post_id = comments.post_id
${insert_2}
${insert_order}
LIMIT ${start}, ${count}
`
    var res = await db.query(sql);
    for (let i = 0; i < res.length; i++){ //以JSON格式存在数据库中的内容，也由dao层负责转换
        res[i].imgs = JSON.parse(res[i].imgs);
        res[i].tags = JSON.parse(res[i].tags);
    }
    return res;
}

async function search_fullcomments1(user_id, post_id, method) {
    var insert_order = method == 'time' ? `ORDER BY com1.comment_time DESC` : `ORDER BY fever DESC`
    var sql = 
`
SELECT
	com1.*,
	upvotes.upvote_num,
	upvotes.upvoted,
	com23.comment_num,
    
    ROUND((IFNULL(upvotes.upvote_num, 0) * 3 +
	IFNULL(com23.comment_num, 0) * 7) * 10 / (10 + TIMESTAMPDIFF(DAY,com1.comment_time,NOW()))) as fever
FROM
	(
		SELECT
			comment.*,
            user.name as user_name,
            user.avatar as user_avatar
		FROM comment, user
        WHERE
			comment.post_id = ${post_id} and
            comment.level = 1 and
			comment.user_id = user.user_id
    ) com1 LEFT OUTER JOIN
	(
		SELECT
			comment_id,
			count(*) AS upvote_num,
			if(sum(user_id = '${user_id}'), true, false) AS upvoted
		FROM upvote_comment
		GROUP BY comment_id
	) upvotes
	ON com1.comment_id = upvotes.comment_id
	LEFT OUTER JOIN
	(
		SELECT
			father_id,
			count(*) AS comment_num
		FROM comment
		GROUP BY father_id
	) com23
	ON com1.comment_id = com23.father_id
${insert_order}
`;
    var res = await db.query(sql);
    return res;
}

async function search_fullcomments23(user_id, father_id) {
    var sql = 
`
SELECT
    com23.*,
    reply_user.name AS reply_user_name,
    upvotes.upvote_num,
    upvotes.upvoted
FROM
	(
		SELECT
			comment.*,
            user.name as user_name,
            user.avatar as user_avatar
		FROM comment, user
        WHERE
			comment.father_id = ${father_id} and
            comment.level > 1 and
			comment.user_id = user.user_id
    ) com23 LEFT OUTER JOIN
    (
        SELECT
            comment_id,
            count(*) AS upvote_num,
            if(sum(user_id = '${user_id}'), true, false) AS upvoted
        FROM upvote_comment
        GROUP BY comment_id
    ) upvotes
    ON com23.comment_id = upvotes.comment_id
    LEFT OUTER JOIN user reply_user
    ON reply_user.user_id = com23.reply_id
ORDER BY com23.comment_time ASC
`
    var res = await db.query(sql);
    return res;
}

module.exports = {
    search_fullposts: search_fullposts,
    search_fullcomments1: search_fullcomments1,
    search_fullcomments23: search_fullcomments23,
}
